package com.yuanshi.faceword

import org.apache.spark.sql.SparkSession

/**
  * 统计用户&内容运营excel表格中的 颜世界内容类型数据 的相关指标
  */
object FaceWordCommentType {

  def main(args: Array[String]): Unit = {
    val spark: SparkSession = SparkSession.builder().enableHiveSupport().config("hive.exec.dynamic.partition", true)
      .config("hive.exec.dynamic.partition.mode", "nonstrict").appName("FaceWordUserData").getOrCreate()

    var dt: String = args(0)

    for (i <- 1 to 2){
    spark.sql(
      s"""
        |select
        |count(if(log_type in (1,2),1,null)), 	-- 视频内容曝光
        |count(if(log_type = 2,1,null)),		-- 视频内容点击
        |count(distinct if(log_type = 2,temporary_id,null)),		-- 视频内容点击人数
        |count(if(log_type = 2,1,null))/count(if(log_type in (1,2),1,null)),		-- 视频ctr
        |count(if(log_type = 2,1,null))/count(distinct temporary_id),
        |count(if(log_type = 2,1,null))/count(distinct if(log_type = 2,temporary_id,null))
        |from
        |(
        |select
        |*
        |from
        |face_value_circle_video_play_info where create_time = '${dt}'
        |) t1
        |join
        |(select * from face_value_circle_video where note_type = ${i} and app_remark = 1 and create_time = '${dt}') t2
        |on t1.temporary_id = t2.id
      """.stripMargin)
    }

    spark.close()
  }
}
